ABOUT Q-DBA (Version 2.1) - NB Only for the 3A ============================================ Updated 13/6/95: Chamges from version 2.0: 1. Can now jump (or "race" --Psion-R!) to specified record number in view/delete and update modes. 2. Can now check for existence of records that have a specified value in first field (In my classical music app: I stick littel labels with a unique "physical number" on each CD , LP, or Tape, but I don't emter data in the Psion in strict physical number order. This is a simple means of establishing whether I've already started entering this data. A couple of other improvements in similar vein haven't yet been coded. 3. You may have found that Q-DBA defaults back to a particular starting field after (e.g.) each new record save. This may not be convenient for you so there's now a means of changing the default to what you'd like. Several relatively minor bugs that I discovered have been put right, including the tendency for primary and secondary list records to pick up additional field values from the first record when you update from add record mode. Still free, subject to request below -- you're still welcome to comment (politely please, I'm sensitive!). All the following is unchanged from my May 29 (ish) upload. ================================================ This is a hugely improved version of a program I uploaded about 3 months ago. All the enhancements promised then - and more - have been made and the user interface has been greatly tidied up. It still essentially exists because I needed something like this but since I've benefited from the efforts of others in the past it seemed only right to make my efforts available to others. I recommend taking a close look at the functionality - please take the time to glance through these notes because while I've tried to put most of the keystroke usage on the screen, there are some very useful hot-keys which you need to know about to get the most out of Q-DBA. This program is free, but if anyone wishes to exploit the ideas behind it commercially I'd like to hear from you first! I don't think there's any likelihood of losing any data under Q-DBA (I've been using it for some time and haven't had any problems) but I need to say that you use the program at your own risk. In return for free use of Q-DBA I'd appreciate comments on improvements and, of course, please let me know if you find any bugs. I've tested it quite extensively and haven't found any significant problems but it'll be much more effective having lots of you looking at it. I realise that a few "expected" functions - like moving a page at a time through record lists - aren't there, but to be frank I filled up two module sets and didn't want to extend to another. If there's enough feedback then obviously I'd consider putting more functionality in. WHY WRITE IT? ============= I don't know if you've ever had this problem, but I found myself adding duplicates to a fast-growing LP & CD collection (mainly of classical music recordings), mainly because I kept no record of what I bought. No problem I thought, I'll use the data app on my 3A. Problem was that I wanted to record oft-repeated data like composer, conductor, orchestra, type of music and recording label. I couldn't face the prospect of re-keying all that repetitive information, so wanted a database which would enable me to access past entries quickly. Also, having found a way of getting my data in quickly, I wanted to be able to open my Psion in Tower Records and punch in something like "Symphony" by "Shostakovitch" conducted by "Klemperer" to see quickly what I already had without having to page through the records laboriously one-by-one! Finally, on a different subject,I wanted to keep house contents details with similarly repetitive categories AND be able to assign values to each entry, find the value of different subsets etc. I also wanted to be able to keep information about numerical values at the same time as being able to view my data in other apps - for sorting or printing purposes perhaps - because I didn't see the point (yet) of coding lots of stuff that's already been done. WHAT DOES Q-DBA DO - IN SUMMARY? (and apart from the obvious!) =============================== A - CHECKING AGAINST LIST FILES ------------------------------- This OPL program uses a DBF file which contains the main records, plus up to 5 ODB "List" files which contain the values which can appear in up to 5 of the main file's datafields. When you add a new record, and reach a field which has an associated list file, the first thing that happens when you start typing a new field value is that Q-DBA offers you the first value in the list file which starts with the letter you just typed. You can either use the right arrow key to browse the list file for subsequent matches, or enter another letter, whereupon another search of the list file is made, this time for entries which start with the two letters typed so far - and so on. Just move to the next field when you're satisfied with what you have. New list file entries can be entered directly, or you can choose to have the list file updated directly from the "Add new main record" process. If the appropriate parameter is set, and you enter a new value in one of the fields associated with a list file, the list file will be updated as soon as you move to the next field. Checking against list files can be "turned off", thus giving a richer range of options for selecting and locating subsets of your records (see later) B. - ABSORPTION OF YOUR EXISTING DBF FILES ------------------------------------------ Q-DBA allows existing files to be "absorbed". All this means is that Q-DBA establishes list files and offers you the opportunity to build initial lists from your existing data fields, as well as reordering the fields to fit more comfortably into Q-DBA's display. You then have the ability to use Q-DBA's list-checking functionality with your existing files. Please note that Q-DBA uses a 10 field record structure -- 2 fields are "Q-DBA specific", namely the unique record number which facilitates some relational capability, and the "code" field, of which more later. What this means though is that you will only be able to display EIGHT fields from absorbed databases. C. - ADDING VALUES TO RECORDS You may add numerical values to records (actually maintained in a separate file, so you can still view and work with your Q-DBA files in the built-in data app etc), total values, edit delete, find values of subsets etc. D. - SELECT RECORDS Via the View/Delete function you may define and then save and/or view a subset of your records. This is done intuitively - i.e. you enter in the record display what values you want the selected records to have (or contain) in up to 3 fields, press TAB and hey presto! (You may have gathered that I like this function). You may view selected records in table or list view and you can change the columns you view, their order and widths. E. - "LOCKED" UPDATE Q-DBA offers a locked update mode - it just means you don't have to press Psion-U each time you want to update a record. F. - VIEW PREVIOUS SELECTION If you saved selected records (see D) then you can view them again in any format you wish. G. - LOCATE MODE This is similar to "select" except that you work with the whole file but "home in" on only those records which match your requirements. You may delete records in this mode. H. - LOCATE/ UPDATE MODE This is the same as E (locked update) except that it operates only on "located" records. This makes it very easy to do a "mass" update on a subset of your file. I. - LIST VIEW This is available either on the whole file (when in View/ Delete mode) - via the Diamond key, or for selected records. (But List files themselves can only be viewed/ updated etc in list view!) WILL Q-DBA ONLY WORK AS A MUSIC OR HOUSE CONTENTS DATABASE? ================================================= No - I decided to allow quite a lot of customisation. Q-DBA uses datasets. The main file associated with each dataset has a minimum of 5 fields and you can add up to 4 further fields. You can decide what each field will be called (more on customisation later). Up to 17 datasets can be created WHAT FILES ARE CREATED/ USED BY Q-DBA? ======================================= A file called default.odb is created and maintained in m:\dba. One of the defaults is the disk for other files, which are: mnames.odb {one record for each dataset, storing name and number of dataset plus main file field names, list file field names and codes associated with that dataset, etc} mainNN.dbf {main data file for dataset number NN, containing 10 fields} subaNN.odb, subbNN.odb {first 2 list files for dataset number NN, containing 5 fields, and described as "Primary List" and "Secondary List"} subc/d/e/NN {3rd 4th and 5th list files for dataset number NN, containing only 1 field} order.odb {stores information about field ordering for absorbed files} ALL these files are stored in directory \DBA\ on the selected disk **************** select.odb {temporary file containing records selected under the "View File" menu item} Saved selections (usernamed) ALL these files are stored in \DBA\SELECT\ on the selected disk **************** valNN.odb {contain values associated with mainNN.dbf} HOW TO INSTALL Q-DBA ===================== Simply copy qdba.opo and q2.opo to an OPO directory, make sure the \DBA\ subdirectory exists on your internal disk and that \DBA\, \DBA\SELECT\ and \DBA\VAL\ exist on the disk you want to store your files in. When you start Q-DBA for the first time, it will look for files in the internal disk. If they're not there it asks you to specify the disk you're using (please be sure to set the preferences too to avoid being asked the same question every time you start - see later for detail of preferences). Then it asks you to specify parameters for the first set. [this happens after you are asked to define the next record number - just accept the proposed value of 1]. [It's probably a good idea to go through all this but for those of you who want a quicker start I've enclosed some specimen files (main01.dbf, suba01.odb, subb01.odb, subc01.odb, subd01.odb sube01.odb and mnames.odb) which need to be located in \DBA\ on your selected disk. If you install these files, Q-DBA should simply start using the few sample records - in a File Set called "Classical Music"] Assuming you do it the hard way but would like a guide for what's required............ The original selections for my Classical Music database (data set 01) were Set Name: Classical Music Field 3: Actual No. (i.e the physical number of the CD - I put stickers numbered 1 to about 900 on my CDs!) Field 4: Opus No. Field 5: Title Field 6: Composer Field 7: Conductor Field 8: Orchestra Field 9: Music Type (Violin Concerto, Symphony, Piano Sonata, etc etc) Field 10. Label Code: Medium Code Value 1: CD Code Value 2: LP Code Value 3: Tape Next Record No: (1 at outset) {this is maintained automatically but you can reset it to tidy the numbering after some time - but do so with care since if duplicate record numbers creep in the functionality will foul up somewhere along the line!} Set to use values?: Yes {you need to "prime" a dataset before you can assign values to records} Composer Extra Field 1: Forename .....2: Year of Birth (DOB) .....3: Year of Death (DOD) .....4: Nationality (COUNTRY) Conductor 1,2,3,4 (similar to above) ************************************* I'd also suggest you set the preferences as follows at least while you're finding your way around having established your first data set as above. List Update Mode - Don't update from Add Record Screen Change Disk for files - as appropriate Initial Startup set no - 01 Show initial set no choices - NO List Check Mode - Check against lists # chars compared with list - 2 MAIN FILE RECORD STRUCTURE ========================== In terms of the Classical Music example above the main record's fields are as follows: Composer, Conductor, Orchestra, Title of piece, Actual CD No, Unique Record No, Opus No, Music Type, Label and Code Value (i.e in this case "1" for CD, "2" for LP and "3" for Tape). Note that the values "1" "2" and "3" are always used irrespective of data set but you can describe these values any way you like. ADDING RECORDS =============== It's best to use "list:/view/del/upd/add" first and add a few items to each list file so you can see the usefulness of Q-DBA more readily. Then move to "add" record (Psion -A) There's a key usage screen so adding records is fairly self-explanatory. The current entry (if you used the Classical Music settings) is defaulted to CD. This is changed by means of Psion-C When you start typing an entry (in any of the last 5 entry boxes) which already exists in the associated list file, you'll see the existing item pop up. Use the right arrow key for the next match with what you typed at the keyboard. You'll see that whatever you actually typed at the keyboard is shown at the bottom of the data entry screen. You can edit the "suggested" entry by using the left arrow key (in this case your keyboard entry is deemed to be the "suggested" entry). Use the Delete key to edit your keyboard entry. The delete key will remove a "suggested" entry altogether but only the final letter of a keyboard entry which has not yet been matched with a list file entry. When ready to save press tab key. You'll be warned if you're trying to save a record for which the "required field" (the 4th on the screen) is left blank. You can also use the diamond key to make a "special save". Use this for a piece that you particularly like or which you want to highlight for some other reason. If you want to add a value, press {Psion +} and complete the dialog before saving the record. Values can be added later via the update function. Note that this way of doing things could lead you to have an "unattached" value for a while (if you don't save the record you were working on in add mode). Because of this, and other possible ways (perhaps a system crash) in which records can become dissociated from values, there's a "tidy values" function which you may be wise to run from time to time. By the way, pressing ENTER in ADD mode has the same effect as the down arrow when the current field is blank, but brings up an edit box otherwise. VIEW/DELETE FUNCTION ===================== Pretty self-explanatory - pressing {Psion- V} for a second time shows the full contents of the field (as does TAB key before you enter select mode - see later}. This is quite useful if you have long fields - you keep the overview of the record on a single screen yet can quickly access full detail of any field. Psion + shows the value and value date (if any) associated with the record (only until you press Diamond key to initiate the toggle - see below) or create a selection from the overall record set. Diamond key toggles list view and table view for whole field. You may select a subset of records (and optionally save it) by entering values in up to 3 fields (any 3 except that you can't use the 4th field which is the basic "description" field in Q-DBA). When you lock into the select process by pressing the TAB or Diamond keys Q-DBA searches for the records with fields which match the values you entered. If (Preferences) you are in "Check Against Lists" mode you can bring up list file values in the normal way and use those in the selection process. Otherwise Q-DBA tries to match your actual entry against the file. Note that Diamond as opposed to TAB will only select those records which were saved as "special" records AND which match the specified fields. After confirming whether or not you want to save the selected records you may toggle between list and table view. Selecting some hot keys from within this "select" mode will revert you to the "ADD mode" screen after your selected hot key function has been performed [the default screen in Q-DBA is ADD, not VIEW]. In table view the only keys available for navigation are ENTER (forward) or SHIFT-ENTER (backwards). Use Psion-W to alter column ordering and sizes when in list view. Enter a letter or other character while in list view to find the first record which starts with that character in the selected field (the default field for this check if the 4th field but you can chose any other field to perform this "first letter paging" on. Each time you press the same letter again Q-DBA looks for the next matching record. Column sizing operates in the window which displays the results of this search. When in table view but after you enter the table/list toggle or after you create a selection, Psion-+ shows the total value associated with the selected records. UPDATE FUNCTION =============== This is fairly self-explanatory. Keys for navigation are as above. You are locked into Update mode, so only need to make required changes then press TAB or diamond to save. Note that TAB saves records previously designated as "special" as "ordinary" records. Diamond saves as special (irrespective of previous designation). If you press Psion-U while in update mode you are presented with an edit box for the current field (doesn't apply to first 3 fields). List file functions apply here as in other modes. Psion-{+} adds or amends the value associated with this record, and Psion-{-} removes the value record. For what it's worth to you, Q-DBA distinguishes between zero value records and main file records which don't have an associated value record. The former show value zero on the display screen and the latter show a hyphen. LOCATE FUNCTION =============== Locate is similar to "Select" as described under the "VIEW/ DELETE" function above. The main differences are: 1. You are working on the whole file, but only have a "window" on records which match the specified fields (further restricted to special records if you lock in using Diamond rather than TAB) 2. You stay in table view. 3. You may delete records. 4. You may convert to "locked update" on matching records by hitting Psion-U when you've located the records you want. This gives you a powerful method for "mass-updating" records that obey the criteria you specify. 5. You have more ways of searching the file. In "Select" mode you can only search based on the record's field matching the value you enter (or, if preference "check against list fields" is turned off, based on the value you enter matching the appropriate number of characters on the left of the field value). On the other hand, in "Locate" mode you can search for records which exactly match the specified fields, or whose fields CONTAIN what you specify. Also, Locate lets you use ANY 3 fields for the search. If you delete enough "located" records or update them so that the fields you originally matched on change, you reduce the size of the record group you're working on and if you reach the point of having no further records which match the original criteria, Q-DBA will "start again" in locate mode, witing for you to specify another set of criteria on which to search. LIST FILE FUNCTIONS =================== You may view list files, add and delete list file records - and update records in primary or secondary list files. All this is done in list view. There is no page view for list files. Note that you can store additional information in primary and secondary list files. FILE SET FUNCTIONS ================== 1. Select Another - self-explanatory 2. Add New Set - self-explanatory 3. Edit Existing Set - self-explanatory 4. Absorb DBF File - similar to 2 except that an existing data file (expected to have extension .dbf) is "absorbed". A duplicated copy of the file is not created; Q-DBA maintains a record of the filename and location and locates the file when this particular fileset is selected. You may edit the filename and location using "Edit Existing Set". When absorbing an existing data file as distinct from adding a new set, two additional options are presented: First, you will have the opportunity to reorder fields in the absorbed database - so that they fit more comfortably into Q-DBA's layout. Then you will be asked if you want to create list files from existing entries in the absorbed database. For what it's worth, this feature enables you to take any DBF file (provided it doesn't have more than 10 fields) and create separate files containing the data from just one field! 5. Delete Existing set. This deletes the list files associated with a particular file set. It also removes that set's parameters from the mnames.odb file, which means that its set number can be reused. Note that the MAIN file (main99.dbf or absorbed file if appropriate) is not deleted - you will need to do this manually. This is done purely for security reasons - it wouldn't have been hard to code that feature! SPECIAL MENU OPTIONS ==================== 1.Field Order changes --------------------- Use with care. This applies only to already-absorbed files - and enables you to change your mind about the field ordering you chose when absorbing the file. If you built list files at outset or have created them since, it's likely that they will be out of phase with the new fields, and if that's the case you'll need to rename the list files manually. 2. View Previous selection -------------------------- You may wish to save sizeable selections, since it can take some time to pare off a subset from a large database. Use this option to view a previous selection. Before viewing, I suggest you change to the file set from which the selection was originally made - otherwise the headings will be those of the current file set and they may not make much sense! Several keys are disabled when you first load the previous selection but Q-DBA tells you which are operative if you attempt to stray from the straight and narrow! 3. Total Value -------------- If the set is primed to take values, this will total all values associated with the present fileset. This is designed to operate on the whole fileset, but if you have selected a subset of records (from view/delete mode) you will only get the total for the selected records. 4. Tidy Values -------------- To allow for little niggles associated with holding values in a separate file, this operation runs through the values file deleting records which don't have an associated main record. The most obvious example of how a niggle might occur is when a value is added in advance of saving a new record -and for some reason the new record doesn't get saved. At the moment I'm not allowing more than one value record per main record so that problem can't get out of hand. 5. Which Columns? ----------------- This only applies to main file records when viewed in list as opposed to page mode. Q-DBA's built-in approach is to show the 4th field value (this is the 1 compulsory field you'll see asterisked in the page mode screen) first and allow the user to pick which other fields - to a maximum of 3 - will be displayed. Column widths can be reset (enabling the first column to be squeezed out if you don't want it shown). Q-DBA uses default fields to display after selecting a subset of records from view/delete mode. If you use the default "standard" select screen size (see preferences later) the small window on the right will show what the selection criteria were. Q-DBA then reasons that you don't need thos fields displayed, and picks the next 3 unspecified fields from your record. If you want to override this (seemingly quite sensible) default you may - and you can also revert to the default display if you wish. At the moment session settings can't be saved. 6. Preferences -------------- A. List update mode: 3 options. "Automatically update" means that whenever you enter a new value in add mode to a field which has an associated list file and then move off that field, the value is stored in the list file. There's no check for duplicates so if you leave this switched on you may have quite a bit of tidying to do! "Don't update from add record screen" and "Ask before updating" are, I hope, self-explanatory! B. Change Disk for Files: this is where you specify the disk for all files apart from default.odb which resides on the internal disk C. Initial Startup Set: what gets fired up when you start Q-DBA D. Show Initial Set Choice: as an alternative to C., this presents a pick-list at start-up. This is somewhat historic and likely to be an irritation for most people, so suggest you set this to "no"! E. List Check Mode: Q-DBA doesn't have to check your data entry against existing lists; use this preference to turn off list checking. If list checking is turned off then when you select records (from view/delete mode) Q-DBA checks your select string against the left of the field in each record. The two alternative settings (compare with left of string and compare with all of string) apply as options only to the "locate" operation. Obviously compare with left of string works as just described. Compare with all of string searches for your specified string anywhere in the appropriate fields. F. Number of characters compared with List. If you have a large file Q-DBA's approach of continually searching lists for a match on the latest value of the input string will be horribly slow. Thus you may specify that it should only search list files if your input string is 1, 2 or 3 characters long. If (for example) you pick 1 character, you're looking for a list file match on the first character only but you will still be able to use the right arrow key to page through alternative matches. G. "Select" screen size. The default shows selection criteria and other data in a small window on the right, so the detail of selected records doesn't occupy the whole Psion screen. If you prefer you can do without the right hand window and have the selected record detail occupy nearly all the Psion screen. Do this by selecting Large (which is a session preference only). Note that the "Large" layout is exactlt the same as the list view window you see when you "list view" the whole file rather than just a selection. TAIL-END ======== I hope this has covered everything you need to know and I hope you find Q-DBA as useful as I do!